package com.etfbl.net.dao;

import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author abosancic
 * @date Jul-29-2013
 */ 

public abstract class DAOBase<T, PK extends Serializable> implements IDAOBase<T, PK> {

    //==========================================================================
    //  property
    //==========================================================================
    protected Class<T> persistentClass = null;
    protected Class[] methodsType = null;
    protected String[] methodsTypeSimpleName = null;
    protected String[] preparedStatementMethodsName = null;
    protected Class[] preparedStatementMethodsType = null;
    protected String[] preparedPrimaryStatementMethodsName = null;
    protected Class[] preparedPrimaryStatementMethodsType = null;
    protected Method [] preparedPrimaryStatment;
    protected Method[] setters = null;
    protected Method[] getters = null;

    public DAOBase(Class<T> persistentClass) {
        this.persistentClass = persistentClass;
        cashedMethodsType();
        cashedPreparedStatementMethodsName();
        cashedPrimaryPreparedStatementMethodsName();
        cashedMethods();
    }

    @Override
    public T find(PK id) {

        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT * FROM " + getTableName()
                    + " WHERE " + appendPrimaryKey();
//            System.out.println("query: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String keys[] = getPrimaryKeyName();
            int num = keys.length;
            
            if (id instanceof BasePK) {
                int indexPK = 1;
                for (int i = 0; i < num; i++) {
                    Method prepareM = preparedStatement.getClass().getMethod(preparedPrimaryStatementMethodsName[i], int.class, preparedPrimaryStatementMethodsType[i]);
                    Method method = id.getClass().getMethod(ClassGeneratorUtil.humanizeToGetter(keys[i]));
                    prepareM.invoke(preparedStatement, indexPK, method.invoke(id));
                    indexPK++;
                }
            } else {
                Method prepareM = preparedStatement.getClass().getMethod(preparedPrimaryStatementMethodsName[0], int.class, preparedPrimaryStatementMethodsType[0]);
                prepareM.invoke(preparedStatement, 1, id);
            }
            ResultSet res = preparedStatement.executeQuery();
            Object object = persistentClass.newInstance();

            String fields[] = getColumns();
            num = fields.length;
            while (res.next()) {
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, fields[i]));
                }
                break;
            }
            //System.out.println("" + object);
            return (T) object;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return null;
    }

    @Override
    public List<T> findAll() {

        List<T> items = new ArrayList<T>();
        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT * FROM " + getTableName();
//            System.out.println("query: " + query);           
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            ResultSet res = preparedStatement.executeQuery();

            String[] columns = getColumns();
            int num = columns.length;

            while (res.next()) {
                Object object = persistentClass.newInstance();
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, columns[i]));
                }
                items.add((T) object);
            }

            System.out.println("num of items: " + items.size());
            res.close();
            preparedStatement.close();

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return items;
    }

    /**
     *
     * @param field
     * @param value
     * @return
     */
    protected List<T> findProcessing(String field, Object value) {

        List<T> items = new ArrayList<T>();
        Connection dbConnection = null;;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT * FROM " + getTableName()
                    + " WHERE " + field + "=?";

            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String fields[] = getColumns();
            int num = fields.length;

            for (int i = 0; i < num; i++) {
                if (field.equals(fields[i])) {
                    Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[i], int.class, preparedStatementMethodsType[i]);
                    prepareM.invoke(preparedStatement, 1, value);
                }
            }

            ResultSet res = preparedStatement.executeQuery();

            while (res.next()) {

                T object = persistentClass.newInstance();

                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, fields[i]));
                }

                items.add(object);
            }

            return items;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return items;
    }

    /**
     *
     * @param range range[0] is current page range[1] is page size
     * @return List<T> items
     */
    public List<T> findRange(int[] range) {

        List<T> items = new ArrayList<T>();
        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT * FROM " + getTableName() + " LIMIT " + range[0] + ", " + range[1];
            System.out.println("query: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            ResultSet res = preparedStatement.executeQuery();

            String[] columns = getColumns();
            int num = columns.length;

            while (res.next()) {
                Object object = persistentClass.newInstance();
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, columns[i]));
                }
                items.add((T) object);
            }

            System.out.println("num of items: " + items.size());
            res.close();
            preparedStatement.close();

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return items;

    }

    @Override
    public boolean remove(PK id) {
        boolean result = false;
        Connection dbConnection = null;
        try {
            if (id == null) {
                return result;
            }

            PreparedStatement preparedStatement = null;
            String query = "DELETE FROM " + getTableName()
                    + " WHERE " + appendPrimaryKey();
//            System.out.println("rquery: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String keys[] = getPrimaryKeyName();
            int num = keys.length;
            
            if (id instanceof BasePK) {
                int indexPK = 1;
                for (int i = 0; i < num; i++) {
                    Method prepareM = preparedStatement.getClass().getMethod(preparedPrimaryStatementMethodsName[i], int.class, preparedPrimaryStatementMethodsType[i]);
                    Method method = id.getClass().getMethod(ClassGeneratorUtil.humanizeToGetter(keys[i]));
                    prepareM.invoke(preparedStatement, indexPK, method.invoke(id));
                    indexPK++;
                }
            } else {
                Method prepareM = preparedStatement.getClass().getMethod(preparedPrimaryStatementMethodsName[0], int.class, preparedPrimaryStatementMethodsType[0]);
                prepareM.invoke(preparedStatement, 1, id);
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }
        return result;
    }

    @Override
    public boolean remove(T t) {
        
        boolean result = false;
        Connection dbConnection = null;
        
        try {

            if (t == null) {
                return result;
            }
            
            PreparedStatement preparedStatement = null;
            String query = "DELETE FROM " + getTableName()
                    + " WHERE " + appendPrimaryKey();
            //System.out.println("rquery: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String keys[] = getPrimaryKeyName();
            int num = keys.length;
            
            String columns[] = getColumns();
            int fileds = columns.length;
            
            int indexPK = 1;
            for (int i = 0; i < num; i++) {
                 for (int j = 0; j < fileds; j++) {
                    if(keys[i].equals(columns[j])){
                        Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[j], int.class, preparedStatementMethodsType[j]);
                        prepareM.invoke(preparedStatement, indexPK, getters[j].invoke(t));
                        indexPK++;
                        break;
                    }
                } 
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            Logger.getLogger(t.getClass().getName()).log(Level.SEVERE, null, t);
        }
        return result;
    }

    protected boolean removeProcessing(String field, Object value) {

        boolean result = false;
        Connection dbConnection = null;
        try {

            if (value == null) {
                return result;
            }

            PreparedStatement preparedStatement = null;
            String query = "DELETE FROM " + getTableName()
                    + " WHERE " + field + "=?";

            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String fields[] = getColumns();
            int num = fields.length;

            for (int i = 0; i < num; i++) {
                if (field.equals(fields[i])) {
                    java.lang.reflect.Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[i], int.class, preparedStatementMethodsType[i]);
                    prepareM.invoke(preparedStatement, 1, value);
                }
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }
        return result;
    }
    
    public boolean removeSLQ(String prepareSQL) {
        return removeSQL(prepareSQL, new Object[]{});
    }
    
    public boolean removeSQL(String prepareSQL, Object... parameter) {
        boolean result = false;
        Connection dbConnection = null;
        try {
            if (prepareSQL == null) {
                return result;
            }

            PreparedStatement preparedStatement = null;
            System.out.println("query: " + prepareSQL + " " + Arrays.asList(parameter));           
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareSQL);
             
            for (int i = 0; i < parameter.length; i++) {
                preparedStatement.setObject(i + 1, parameter[i]);
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }
        return result;
    }

    @Override
    public boolean save(T t) {
        boolean result = false;
        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareStatmentQuery(t));

            String fields[] = getColumns();
            int num = fields.length;

            for (int i = 0; i < num; i++) {
                Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[i], int.class, preparedStatementMethodsType[i]);
                prepareM.invoke(preparedStatement, (i + 1), getters[i].invoke(t));
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }
        return result;
    }

    @Override
    public boolean save(List<T> values) {
        boolean result = false;
        Connection dbConnection = null;
        try {
            String fields[] = getColumns();
            int num = fields.length;
            PreparedStatement preparedStatement = null;
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareStatmentQuery(values.get(0)));
            dbConnection.setAutoCommit(false);
            for (T t : values) {
                for (int i = 0; i < num; i++) {
                    Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[i], int.class, preparedStatementMethodsType[i]);
                    prepareM.invoke(preparedStatement, (i + 1), getters[i].invoke(t));
                }
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            dbConnection.commit();
            dbConnection.setAutoCommit(true);

        } catch (Exception ex) {
            try {
                dbConnection.rollback();
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex1) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex1);
            }
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return result;
    }

    @Override
    public boolean update(T t) {
        boolean result = false;
        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareStatmentQueryUpdate(t));
            String fields[] = getColumns();
            int num = fields.length;

            for (int i = 0; i < num; i++) {
                Method prepareM = preparedStatement.getClass().getMethod(preparedStatementMethodsName[i], int.class, preparedStatementMethodsType[i]);
                prepareM.invoke(preparedStatement, (i + 1), getters[i].invoke(t));
            }
            
            String keys[] = getPrimaryKeyName();
            int key = keys.length;
            
            // find primary key
            for (int j = 0; j < key; j++) {
                Method prepareM = preparedStatement.getClass().getMethod(preparedPrimaryStatementMethodsName[j], int.class, preparedPrimaryStatementMethodsType[j]);
                prepareM.invoke(preparedStatement, (num + j + 1), preparedPrimaryStatment[j].invoke(t));
            }

            result = (preparedStatement.executeUpdate() > 0) ? true : false;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }
        return result;
    }

    @Override
    public List<T> query(String prepareSQL) {
        return query(prepareSQL, new Object[]{});
    }

    @Override
    public List<T> query(String prepareSQL, Object... parameter) {

        List<T> items = new ArrayList<T>();
        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;
            System.out.println("query: " + prepareSQL + " " + Arrays.asList(parameter));
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareSQL);

            for (int i = 0; i < parameter.length; i++) {
                preparedStatement.setObject(i + 1, parameter[i]);
            }

            ResultSet res = preparedStatement.executeQuery();

            String[] columns = getColumns();
            int num = columns.length;

            while (res.next()) {
                Object object = persistentClass.newInstance();
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, columns[i]));
                }
                items.add((T) object);
            }

            System.out.println("num of items: " + items.size());
            res.close();
            preparedStatement.close();

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return items;
    }
    
     /**
     *
     * @param prepareSQL
     * @return
     */
    @Override
    public ResultSet selectResultSet(String prepareSQL){
        return selectResultSet(prepareSQL, new Object[]{});
    }
    
    /**
     *
     * @param prepareSQL
     * @param parameter
     * @return
     */
    @Override
    public ResultSet selectResultSet(String prepareSQL, Object... parameter){
    
        ResultSet res = null;
        Connection dbConnection = null;
        
        try {
            
            PreparedStatement preparedStatement = null;
            System.out.println("query: " + prepareSQL + " " + Arrays.asList(parameter));           
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(prepareSQL);
             
            for (int i = 0; i < parameter.length; i++) {
                preparedStatement.setObject(i + 1, parameter[i]);
            }
            
            res = preparedStatement.executeQuery();
            
            //System.out.println("num of items: " + res.getFetchSize());
            // do not close res and prepearedStatment
            // when return resultset
            //res.close();
            //preparedStatement.close();
            
        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return res;
    }

    public int countRows() {
        
        int totalRows = 0;

        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT COUNT(*) FROM " + getTableName();
            //System.out.println("query: " + query);           
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            ResultSet res = preparedStatement.executeQuery();
            
            res.next();
            totalRows = res.getInt(1);

            //System.out.println("num of items: " + totalRows);
            res.close();
            preparedStatement.close();

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return totalRows;
        
    }
    
    @Override
    public T getLast() {

        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            int count = countRows();
            String query = "SELECT * FROM " + getTableName() + " LIMIT " + (count -1) + " , " + count;
//            System.out.println("query: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String fields[] = getColumns();
            int num = fields.length;

            ResultSet res = preparedStatement.executeQuery();
            Object object = persistentClass.newInstance();

            while (res.next()) {
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, fields[i]));
                }
                break;
            }
            //System.out.println("" + object);
            return (T) object;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return null;
    }

    @Override
    public T getFirst() {

        Connection dbConnection = null;
        try {

            PreparedStatement preparedStatement = null;

            String query = "SELECT * FROM " + getTableName() + " LIMIT 1";
//            System.out.println("query: " + query);
            dbConnection = ConnectionPool.getConnectionPool().checkOut();
            preparedStatement = dbConnection.prepareStatement(query);

            String fields[] = getColumns();
            int num = fields.length;

            ResultSet res = preparedStatement.executeQuery();
            Object object = persistentClass.newInstance();

            while (res.next()) {
                for (int i = 0; i < num; i++) {
                    setters[i].invoke(object, getMapperDB(methodsTypeSimpleName[i], res, fields[i]));
                }
                break;
            }
            //System.out.println("" + object);
            return (T) object;

        } catch (Exception ex) {
            Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            ConnectionPool.getConnectionPool().checkIn(dbConnection);
        }

        return null;
    }

    //==========================================================================
    // protected methods
    //==========================================================================
    protected String [] getPrimaryKeyName() {
        return null;
    }

    protected String getTableName() {
        return null;
    }

    protected String[] getColumns() {
        return null;
    }

    protected Object getMapperDB(String type, ResultSet res, String columnName) throws SQLException {

        if (type.equals("Short") || type.equals("Short")) {
            return res.getShort(columnName);
        } else if (type.equals("Integer")) {
            return res.getInt(columnName);
        } else if (type.equals("Float")) {
            return res.getFloat(columnName);
        } else if (type.equals("String")) {
            return res.getString(columnName);
        } else if (type.equals("boolean")) {
            return res.getBoolean(columnName);
        } else if (type.equals("Time")) {
            return res.getTime(columnName);
        } else if (type.equals("Timestamp")) {
            return res.getTimestamp(columnName);
        } else if (type.equals("Date")) {
            return res.getDate(columnName);
        } else if (type.equals("Double") || type.equals("double")) {
            return res.getDouble(columnName);
        } else if (type.equals("BigDecimal")) {
            return res.getBigDecimal(columnName);
        } else if (type.equals("Long") || type.equals("long")) {
            return res.getLong(columnName);
        } else if (type.equals("Blob")) {
            return res.getBlob(columnName);
        } else if (type.equals("InputStream")) {
            return res.getBinaryStream(columnName);
        } else if (type.equals("Byte") || type.equals("byte")) {
            return res.getByte(columnName);
        }
        return null;
    }

    protected Class getChangeClass(String type) {
        
        if (type.equals("Short") || type.equals("Short")) {
            return short.class;
        } else if (type.equals("Integer")) {
            return int.class;
        } else if (type.equals("Float") || type.equals("float")) {
            return float.class;
        } else if (type.equals("String")) {
            return String.class;
        } else if (type.equals("boolean") || type.equals("Boolean")) {
            return boolean.class;
        } else if (type.equals("Time")) {
            return Time.class;
        } else if (type.equals("Timestamp")) {
            return Timestamp.class;
        } else if (type.equals("Date")) {
            return Date.class;
        } else if (type.equals("Double") || type.equals("double")) {
            return double.class;
        } else if (type.equals("BigDecimal")) {
            return BigDecimal.class;
        } else if (type.equals("Long") || type.equals("long")) {
            return long.class;
//        } else if (type.equals("Blob") || type.equals("Short")) {
//            return Blob;
        } else if (type.equals("InputStream")) {
            return InputStream.class;
        } else if (type.equals("Byte") || type.equals("byte")) {
            return byte.class;
        }
        
        return null;
    }

    protected String prepareStatmentQuery(T t) {

        String insertValue = "";
        String insertColumn = "";

        String fields[] = getColumns();

        for (String field : fields) {
            insertValue += "?,";
            insertColumn += field + ", ";
        }

        String insertQuery = "INSERT INTO " + getTableName() + " (" + insertColumn.substring(0, insertColumn.length() - 2) + ")"
                + " VALUES (" + insertValue.substring(0, insertValue.length() - 1) + ")";
//        System.out.println("insert preparestatment query: " + insertQuery);
        return insertQuery;
    }

    //==========================================================================
    // private methods
    //==========================================================================
    private void cashedMethods() {

        String[] fields = getColumns();

        setters = new Method[fields.length];
        getters = new Method[fields.length];

        int num = fields.length;

        for (int i = 0; i < num; i++) {
            try {
//                System.out.println("Found a method: " + ClassGeneratorUtil.humanizeToGetter(fields[i]) + 
//                        " in class " + ClassGeneratorUtil.humanize(getTableName()));
//                System.out.println("Found a method: " + ClassGeneratorUtil.humanizeToSetter(fields[i]) + 
//                        " in class " + ClassGeneratorUtil.humanize(getTableName()));
                Method mg = persistentClass.getMethod(ClassGeneratorUtil.humanizeToGetter(fields[i]), null);
                Method ms = persistentClass.getMethod(ClassGeneratorUtil.humanizeToSetter(fields[i]), this.methodsType[i]);
                getters[i] = mg;
                setters[i] = ms;
            } catch (Exception ex) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    private void cashedMethodsType() {

        String[] fields = getColumns();
        this.methodsType = new Class[fields.length];
        this.methodsTypeSimpleName = new String[fields.length];
        
        int num = fields.length;

        for (int i = 0; i < num; i++) {
            try {
                Method method = persistentClass.getMethod(ClassGeneratorUtil.humanizeToGetter(fields[i]), null);
                this.methodsType[i] = method.getReturnType();
                this.methodsTypeSimpleName[i] = method.getReturnType().getSimpleName();
//                System.out.println("Found a type: " + ClassGeneratorUtil.humanizeToGetter(fields[i]) + 
//                        " in class " + ClassGeneratorUtil.humanize(getTableName()));
            } catch (Exception ex) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        String[] keys = getPrimaryKeyName();
        int keysNum = keys.length;
        
        this.preparedPrimaryStatment = new Method[keysNum];
        
        for (int i = 0; i < keysNum; i++) {
            try {
                Method method = persistentClass.getMethod(ClassGeneratorUtil.humanizeToGetter(keys[i]), null);
                this.preparedPrimaryStatment[i] = method;
//                System.out.println("Found a type: " + ClassGeneratorUtil.humanizeToGetter(fields[i]) + 
//                        " in class " + ClassGeneratorUtil.humanize(getTableName()));
            } catch (Exception ex) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }    

    }

    private void cashedPreparedStatementMethodsName() {

        String[] fields = getColumns();
        this.preparedStatementMethodsName = new String[fields.length];
        this.preparedStatementMethodsType = new Class[fields.length];
        int num = fields.length;

        for (int i = 0; i < num; i++) {
            try {
                Class type = getChangeClass(methodsType[i].getSimpleName());
                this.preparedStatementMethodsName[i] = ClassGeneratorUtil.humanizeToSetter(type.getSimpleName());
                this.preparedStatementMethodsType[i] = type;
            } catch (Exception ex) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }

    private void cashedPrimaryPreparedStatementMethodsName() {

        String[] keys = getPrimaryKeyName();
        int num = keys.length;
        this.preparedPrimaryStatementMethodsName = new String[num];
        this.preparedPrimaryStatementMethodsType = new Class[num];

        for (int i = 0; i < num; i++) {
            try {
                Method method = persistentClass.getMethod(ClassGeneratorUtil.humanizeToGetter(keys[i]), null);
                Class type = getChangeClass(method.getReturnType().getSimpleName());
                this.preparedPrimaryStatementMethodsName[i] = ClassGeneratorUtil.humanizeToSetter(type.getSimpleName());
                this.preparedPrimaryStatementMethodsType[i] = type;
            } catch (Exception ex) {
                Logger.getLogger(DAOBase.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

    }
     
    private String prepareStatmentQueryUpdate(T t) {

        String fields[] = getColumns();

        String update = "UPDATE " + getTableName() + " SET ";

        for (String field : fields) {
            update += (field + "=?, ");
        }

        update = update.substring(0, update.length() - 2) + " WHERE " + appendPrimaryKey();
        System.out.println("update preparestatment query: " + update);
        return update;
    }

    protected String appendPrimaryKey() {
        String rez = "";
        String[] keys = getPrimaryKeyName();
        for (String key : keys) {
            rez += key + " = ? AND ";
        }

        return rez.substring(0, rez.length() - 5);
    }
}
